const express = require('express');
const router = express.Router();
const R = require("../util/json")
const connection = require("../util/db")
const json = require("../util/jsonFormat")

router.get('/selectRole',function (req,res) {
    const sql = "select r.role_name name,count(u.role_id) count from userinfo u right join role r on u.role_id = r.id group by r.role_name";
    connection.query(sql,[],function (e,r) {
        if (e){
            throw e;
        }
        res.json(R.data(json.formatArray(r)))
    })
})
router.get('/selectDept',function (req,res) {
    const sql = "select d.department_name name,count(u.id) count from userinfo u right join department d on u.department_id = d.id group by d.department_name";
    connection.query(sql,[],function (e,r) {
        if (e){
            throw e;
        }
        res.json(R.data(json.formatArray(r)))
    })
})
router.get('/selectApply',function (req,res) {
    const sql = 'select u.nickname name,count(a.apply_id) count from userinfo u left join apply a on u.id = a.apply_id where u.role_id not in(1,4) group by u.nickname order by count desc';
    connection.query(sql,[],function (e,r) {
        if (e){
            throw e;
        }
        res.json(R.data(json.formatArray(r)))
    })
})
router.get('/selectCar',function (req,res) {
    const sql = 'select count(type) as value from car group by type order by type';
    connection.query(sql,[],function (e,r) {
        if (e){
            throw e;
        }
        res.json(R.data(json.formatArray(r)))
    })
})

module.exports = router;
